﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;
using System.Data;
using System.Windows.Forms;


namespace FWMS.DB
{
    class DBbase
    {
 		OleDbConnection myConn;
		OleDbCommand myCmd;
		OleDbDataReader myDataReader;
		private int position;
		public object [,]rows;
		private string lastSelect;
        public int Col_Number;
        public int Row_Number;

        public DBbase()
		{
            myConn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=emsfina.mdb");
			myCmd=new OleDbCommand();
			myCmd.Connection=myConn;
			myDataReader=null;
			position=0;
			rows=new object[0,0];
			lastSelect="";
		}

		private int GetColumnNumber(string commandText)
		{
			try
			{
				myConn.Open();
				myCmd.CommandText=commandText;
				myDataReader=myCmd.ExecuteReader();
				int Col_Number=myDataReader.FieldCount;
				return  Col_Number;
			}
			catch
			{
				return -1;
			}
			finally
			{
				myConn.Close();
			}
		}

		private int GetRowNumber(string commandText)
		{
			try
			{
				myConn.Open();
				myCmd.CommandText=commandText;
				myDataReader=myCmd.ExecuteReader();
				int Row_Number=0;
				while(myDataReader.Read()) ++Row_Number;
				return Row_Number;
			}
			catch
			{
				return -1;
			}
			finally
			{
				myConn.Close();
			}
		}

		private bool Select(string commandText)
		{

			position=0;
			int Row_Pos=0;
			int Col_Pos=0;
			Row_Number=GetRowNumber(commandText);
			Col_Number=GetColumnNumber(commandText);
			rows=new object[Row_Number,Col_Number];
			try
			{
				myConn.Open();
				myCmd.CommandText=commandText;
				myDataReader=myCmd.ExecuteReader();
				while(myDataReader.Read())
				{
					for(Col_Pos=0;Col_Pos<Col_Number;Col_Pos++)
						rows[Row_Pos,Col_Pos]=myDataReader[Col_Pos];
					++Row_Pos;
				}
				lastSelect=commandText;
				return true;
			}
			catch
			{
				return false;
			}
			finally
			{
				myConn.Close();
			}
		}

		private bool NonQuery(string commandText)
		{
			try
			{
				myConn.Open();
				myCmd.CommandText=commandText;
				myCmd.ExecuteNonQuery();
				return true;
			}
			catch(Exception e1)
			{
				MessageBox.Show(e1.Message);
				myConn.Close();
				return false;
			}
			finally
			{
				myConn.Close();
			}
		}

		private string trimSpace(string str)
		{
			while(str.Substring(0,1)==" ") str=str.Substring(1);
			return str;
		}

		public bool ExecuteSQL(string commandText)
		{
			commandText=trimSpace(commandText);
			if(commandText.Substring(0,6).ToLower()=="select") return Select(commandText);
			else
			{
				if(NonQuery(commandText)==false)
				{
					if(lastSelect!="") Select(lastSelect);
					return false;
				}
				else 
				{
					if(lastSelect!="") Select(lastSelect);
					return true;
				}
			}
		}

		public bool pointToNext()
		{
			if(position<rows.GetLength(0)-1)
			{
				++position;
				return true;
			}
			else return false;
		}

		public bool pointToPrev()
		{
			if(position>0)
			{
				--position;
				return true;
			}
			else return false;
		}

		public bool pointToFirst()
		{
			if(rows.GetLength(0)!=0)
			{
				position=0;
				return true;
			}
			else return false;
		}

		public bool pointToLast()
		{
			if(rows.GetLength(0)!=0)
			{
				position=rows.GetLength(0)-1;
				return true;
			}
			else return false;
		}

		public int getPos()
		{
			return position;
		}
        public bool command()
        {
            myConn.Open();
            OleDbTransaction transaction = myConn.BeginTransaction();
            transaction.Commit();
            myConn.Close();
            return true;
        }
    }
}
